What is a case when statement in Snowflake?

The CASE WHEN statement in Snowflake works like a chain of "if-then" statements. It checks a list of conditions sequentially and returns a result for the first true condition. If no conditions are true, it can return a specified default result using an ELSE, or it will return NULL if ELSE is not provided.

What is short-circuiting in Snowflake?

Short-circuiting: Snowflake evaluates all `OR` conditions in a `CASE` statement, even if the first condition evaluates to true. This behavior is different from some programming languages where evaluation stops at the first true condition (short-circuiting).

Does Snowflake support count_if?

Snowflake supports COUNT_IF: Returns the number of records that satisfy a condition. Aggregate function WEEKOVERYEAR(DATETIME) AS WEEK, COUNT_IF(CATEGORY = 'A') AS num_a COUNT () counts the number of rows that are not null. If you are want when ID is not null AND CATEGORY = 'A' then will give you that, or you can use a SUM like in Gordon's answer